import pandas as pd
from openpyxl import Workbook
import encry_excel as encry

# 读取工资单总表
filePath = r'D:\Users\12717\Downloads\python\code\工资单.xlsx'
df = pd.read_excel(filePath, sheet_name='Sheet1')
# 获取标题行
titleRow = df.columns.values.tolist()

# 创建新的Workbook对象
new_workbook = Workbook()

# 遍历所有行数据
for row in df.values:
    # 员工编号，第一列
    empId = row[0]
    # 姓名，第二列
    empName = row[1]
    # 身份证号码，第三列
    idcard = str(row[2])
    print(empId, empName, idcard, idcard[-6:])
    # 获取活动的Worksheet
    ws = new_workbook.active
    # 将当前行的数据复制到新的Worksheet
    for i in range(len(row)):
        # 先复制标题行
        ws.cell(row=1, column=i + 1, value=titleRow[i])
        # 再复制当前行
        ws.cell(row=2, column=i + 1, value=str(row[i]))
    # 保存新的Excel文件
    file_path = fr'D:\Users\12717\Downloads\python\code\员工工资单\{empId}_{empName}_工资单.xlsx'
    new_workbook.save(file_path)
    # 加密
    encry.encrypt_excel_win32(file_path, idcard[-6:])
